package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.DeptPic;
import sy.pageModel.Record;
import sy.service.RecordServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-07-12.
 */
@Service
public class RecordServiceImpl implements RecordServiceI {

    @Autowired
    private JdbcTemplate template;

    @Override
    public DataGrid findRecordDataGird(Record currency) {
        DataGrid dg = new DataGrid();
        String sql = "select count(*) from xj_transaction_record where 1=1 ";
        sql = addWhere(currency, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "SELECT *from xj_transaction_record where 1=1  ";
        sql = addWhere(currency, sql);
        sql = addOrderBy(currency, sql);
        sql = addLimit(currency, sql);
        final List<Record> currencyList = new ArrayList<Record>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                Record record=new Record();
                record.setId(rs.getString("id"));
                record.setTypeId(rs.getString("typeid"));
                record.setCreateTimeStart(rs.getString("t_time"));
                record.setMoney(rs.getString("money"));
                record.setBalance(rs.getString("balance"));
                record.setTnumber(rs.getString("t_number"));
                record.setOrderNumber(rs.getString("order_number"));
                record.setUserId(rs.getString("vnumber"));
                currencyList.add(record);
            }});
        dg.setRows(currencyList);
        return dg;
    }

    @Override
    public List findNameList() {
        String sql="SELECT  DISTINCT vnumber FROM xj_transaction_record";
        final List<BaseEntity> entityList=new ArrayList<>();
        template.query(sql, new Object[]{}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(resultSet.getString("vnumber"));
                entity.setText(resultSet.getString("vnumber"));
                entityList.add(entity);
            }
        });
        return entityList;
    }


    private String addLimit(Record currency, String sql) {
        int fromRow = (currency.getPage() - 1) * currency.getRows();
        sql += " limit " + fromRow + "," + currency.getRows();
        return sql;
    }

    private String addWhere(Record currency, String sql) {
        if(!StringUtil.isNullOrEmpty(currency.getTypeId())) sql += " and typeid = '" + currency.getTypeId() + "'";
        if(!StringUtil.isNullOrEmpty(currency.getUserId())) sql += " and vnumber = '" + currency.getUserId() + "'";
        if(!StringUtil.isNullOrEmpty(currency.getCreateTimeStart())) sql += " and t_time >= '" + currency.getCreateTimeStart() + "'";
        if(!StringUtil.isNullOrEmpty(currency.getCreateTimeEnd())) sql += " and t_time <= '" + currency.getCreateTimeEnd() + "'";
        return sql;
    }

    private String addOrderBy(Record currency, String sql) {
        if (currency.getSort() != null) {
            if("typeName".equals(currency.getSort())) currency.setSort("typeId");
            if("userId".equals(currency.getSort())) currency.setSort("vnumber");
            if("createTimeStart".equals(currency.getSort())) currency.setSort("t_time");
            sql += " order by " + currency.getSort() + " " + currency.getOrder();
        }
        return sql;
    }
}
